"""
用户分群相关接口
"""
from fastapi import APIRouter, BackgroundTasks
from fastapi import Request
from starlette.templating import Jinja2Templates

from schemas import schema_template
from models import model_sql
from bitools import DB
import pandas as pd
import time

router_template = APIRouter(prefix='/data_template', tags=['SQL模板系统'])
templates = Jinja2Templates(directory="templates")


# 运行SQL脚本
async def execute_sql(task_id, template_sql, task_parm, engine_id):
    try:
        engine = await model_sql.TemplateEngine.get_or_none(engine_id=engine_id)
        print(type(task_parm))
        for parm in task_parm:
            print(f"${{{parm}}}", task_parm[parm])
            template_sql = template_sql.replace("${" + parm + "}", task_parm[parm])
            print(template_sql)
        db = DB(engine.engine_config)
        df = db.select(template_sql, kind=True)
        db.close()
        df.to_excel(f"任务{task_id}AT{int(time.time())}.xlsx", index=False)
        await model_sql.TemplateTask.filter(task_id=task_id).update(
            task_status='执行成功',
            task_result=f"任务{task_id}AT{int(time.time())}.xlsx"
        )
    except:
        await model_sql.TemplateTask.filter(task_id=task_id).update(
            task_status='执行失败',
            task_result='None')
    return {"task_id": task_id}


@router_template.get('/', summary='查询所有模板')
async def get_template_list(request: Request, page: int, limit: int):
    """分页查询列表"""
    items = await model_sql.Templates.all().offset(page).limit(limit)
    return templates.TemplateResponse("index.html", {"request": request, "items": items})


@router_template.post('/', summary='新增模板')
async def add_template_detail(item: schema_template.Templates):
    item = await model_sql.Templates.create(template_title=item.template_title,
                                            template_desc=item.template_desc,
                                            template_sql=item.template_sql,
                                            template_parm=item.template_parm,
                                            template_engine_id=item.template_engine_id)
    return item


@router_template.get('/{template_id}', summary='获取模板信息')
async def get_template_detail(template_id: int):
    item = await model_sql.Templates.get(template_id=template_id)
    return item


@router_template.put('/{template_id}', summary='修改模板信息')
async def update_template_detail(template_id: int, item: schema_template.Templates):
    item = await model_sql.Templates.filter(template_id=item.template_id).update(template_id=item.template_id,
                                                                                 template_title=item.template_title,
                                                                                 template_desc=item.template_desc,
                                                                                 template_sql=item.template_sql,
                                                                                 template_parm=item.template_parm,
                                                                                 template_engine_id=item.template_engine_id)
    return item


@router_template.delete('/{template_id}', summary='删除模板信息')
async def delete_template_detail(template_id: int):
    item = await model_sql.Templates.filter(template_id=template_id).delete()
    return item


@router_template.get('/task/', summary='查询所有任务')
async def get_task_list(page: int, limit: int):
    """分页查询列表"""
    item = await model_sql.TemplateTask.all().offset(page).limit(limit)
    return item


@router_template.get('/add_task/{template_id}', summary='创建任务')
async def add_task_detail(request: Request, template_id: int):
    item = await model_sql.Templates.get_or_none(template_id=template_id)
    return templates.TemplateResponse("create_task.html", {"request": request, "item": item})


@router_template.post('/task/', summary='创建任务')
async def add_task_detail(item: schema_template.TemplateTask, background_tasks: BackgroundTasks):
    template = await model_sql.Templates.get_or_none(template_id=item.template_id)
    task = await model_sql.TemplateTask.create(
        template_id=item.template_id,
        task_parm=item.task_parm,
        task_result='运行中',
        task_status=item.task_status)
    background_tasks.add_task(execute_sql,
                              task.task_id,
                              template.template_sql,
                              item.task_parm,
                              template.template_engine_id)
    return task
